package org.example.dao;

import org.example.entity.Category;
import org.example.entity.Record;
import org.example.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * DAO类-Category，对category表进行增改删查操作，消费种类数据表的操作
 */
public class CategoryDAO {
    /**
     * 新增消费种类
     *
     * @param name 消费种类的名称
     */
    public static void add(String name) {
        String sql = "insert into category (`name`) values (?)";
        try (Connection c = DBUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 修改消费种类的名称 name
     *
     * @param id   查找
     * @param name 新的消费种类名称
     */
    public static void update(int id, String name) {
        String sql = "update category set name = ? where id = ?";
        try (Connection c = DBUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setInt(2, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 通过查找 id 删除消费种类
     *
     * @param id 待删除的消费种类的 id
     */
    public static void delete(int id) {
        String sql = "delete from category where id = ?";
        try (Connection c = DBUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 通过查找 id 返回 Category （消费种类）的对象
     *
     * @param id 消费种类的 id
     * @return Category （消费种类）的对象
     */
    public static Category getCategoryById(int id) {
        Category category = null;
        String sql = "select * from category where id = ?";
        try (Connection c = DBUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                category = new Category(rs.getInt("id"), rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return category;
    }


    /**
     * 查询出所有的 消费种类
     * desc 降序， asc 升序
     *
     * @return List\<Category>
     */
    public static List<Category> listAllCategory() {
        String sql = "select * from category order by id desc";
        List<Category> categoryList = new ArrayList<>();
        try (Connection c = DBUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                // 计算所有年月的总次数 recordNumber、计算所有年月的总金额 recordTotalSpend
                List<Record> recordList = RecordDAO.listByCategoryId(rs.getInt("id"));
                double recordTotalSpend = 0;
                for (Record record : recordList) {
                    recordTotalSpend += record.getSpend();
                }
                Category category = new Category(rs.getInt("id"), rs.getString("name"));
                category.setRecordNumber(recordList.size());
                category.setRecordTotalSpend(recordTotalSpend);
                categoryList.add(category);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        categoryList.sort((c1, c2) -> c2.getRecordNumber() - c1.getRecordNumber());     // 降序
        return categoryList;
    }


    /**
     * 用于绘制扇形图的数据
     *
     * @return List\<Category>
     */
    public static List<Category> statisticData() {
        List<Record> listThisMonth = RecordDAO.listThisMonth();
        List<Category> categoryList = listAllCategory();
        for (Category category : categoryList) {
            int recordNumber = 0;
            double recordTotalSpend = 0;
            for (Record record : listThisMonth) {
                if (record.getCid() == category.getId()) {
                    recordNumber += 1;
                    recordTotalSpend += record.getSpend();
                }
            }
            category.setRecordNumber(recordNumber);
            category.setRecordTotalSpend(recordTotalSpend);
        }
        return categoryList;
    }
}
